﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
namespace TuyennvLibrary.SqlClient
{
    // Summary:
    //     Lop dung thao tac voi SQL
    public class SqlHelper
    {
        private SqlConnection connect = new SqlConnection();
        
        public SqlHelper()
        {
            this.ConnectionString=ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;            
        }
        /// <summary>
        /// Khởi tạo
        /// </summary>
        /// <param name="ConnectionString">Chuỗi connect tới Sql</param>
        public SqlHelper(string ConnectionString)
        {
            this.ConnectionString = ConnectionString;
        }
        /// <summary>
        /// Khởi tạo
        /// </summary>
        /// <param name="ConnectionStringName">Tên name của chuỗi connect trong file config</param>
        /// <param name="WebConfig">True: ConnectionStringName theo file config, False: ConnectionStringName mặc định là "ConnectionString"</param>
        public SqlHelper(string ConnectionStringName, bool WebConfig)
        {
            if(WebConfig)
                this.ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
            else this.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }
        public string ConnectionString { get; set; }
        /// <summary>
        /// Mở kết nối tới Sql
        /// </summary>
        public bool Open()
        {
            try
            {
                this.connect.ConnectionString = ConnectionString;
                this.connect.Open();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("loi ket noi");
            }
        }
        /// <summary>
        /// Đóng kết nối với Sql
        /// </summary>
        private void Close()
        {
            this.connect.Close();
        }
        #region Method Object

        /// <summary>
        /// Thực thi câu lệnh Sql, trả về một DataTable
        /// </summary>
        /// <param name="strQuerry">Chuỗi Sql</param>
        /// <param name="parameters">Giá trị các biến trong chuỗi Sql</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strQuerry, SqlParameter[] parameters)
        {
            DataTable result = null;
            Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(strQuerry, connect))
                {
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        da.SelectCommand = cmd;
                        da.Fill(dt);
                        result = dt;

                    }

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return result;
        }
        public DataTable ExecuteDataTable(string strQuerry)
        {
            return ExecuteDataTable(strQuerry, null);
        }
        public Object ExecuteScalar(string strQuerry, SqlParameter[] parameters)
        {
            Object result = null;
            Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(strQuerry, connect))
                {
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);
                    result = cmd.ExecuteScalar();

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return result;
        }
        /// <summary>
        /// Thực thi câu lệnh Sql, trả về một SqlDataReader
        /// </summary>
        /// <param name="strQuerry">Chuỗi Sql</param>
        /// <param name="parameters">Giá trị các biến trong chuỗi Sql</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string strQuerry, SqlParameter[] parameters)
        {
            SqlDataReader result = null;
            Close();
            Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(strQuerry, connect))
                {
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);
                    result = cmd.ExecuteReader();

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
            }
            return result;
        }
        /// <summary>
        /// Thực thi câu lệnh Sql, trả về một DataSet
        /// </summary>
        /// <param name="query">Chuỗi Sql</param>
        /// <param name="param">Giá trị các biến trong chuỗi Sql</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, SqlParameter[] param)
        {
            DataSet dt = null;
            Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(query, connect))
                {
                    if (param != null)
                        cmd.Parameters.AddRange(param);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        DataSet ds = new DataSet();
                        da.SelectCommand = cmd;
                        ds.Clear();
                        da.Fill(ds);
                        dt = ds;
                    }

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return dt;
        }
        /// <summary>
        /// Thực thi câu lệnh Sql
        /// </summary>
        /// <param name="query">Chuỗi Sql</param>
        /// <param name="param">Giá trị các biến trong chuỗi Sql</param>
        /// <returns></returns>
        public int ExecuteQuery(string query, SqlParameter[] param)
        {
            int result;
            try
            {
                Open();
                using (SqlCommand cmd = new SqlCommand(query, connect))
                {
                    if (param != null)
                        cmd.Parameters.AddRange(param);
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return result;
        }
        public int ExecuteStoreProcedure(string storeProcedure, SqlParameter[] parameters)
        {
            int result=-1;
            try
            {
                Open();
                using (SqlCommand cmd = new SqlCommand(storeProcedure, connect))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);
                    result = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return result;
        }
        public DataTable ExecuteStoreProcedureDataTable(string storeProcedure, SqlParameter[] parameters)
        {
            DataTable result = null;
            Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(storeProcedure, connect))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        da.SelectCommand = cmd;
                        da.Fill(dt);
                        result = dt;

                    }

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return result;
        }

        public DataSet ExecuteStoreProcedureDataSet(string storeProcedure, SqlParameter[] parameters)
        {
            DataSet result = null;
            Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(storeProcedure, connect))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        DataSet ds = new DataSet();
                        da.SelectCommand = cmd;
                        da.Fill(ds);
                        result = ds;

                    }

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return result;
        }
        #region Select DataSet
        /// <summary>
        /// Truy vấn Sql theo tên bảng
        /// </summary>
        /// <param name="tableName">Tên bảng cần truy vấn</param>
        /// <returns></returns>
        public DataSet SelectDataSet(string tableName)
        {
            string strQuerry = "select * from " + tableName;
            return ExecuteDataSet(strQuerry, null);
        }
        /// <summary>
        /// Truy vấn Sql theo tên bảng
        /// </summary>
        /// <param name="fields">Mảng tên các cột cần lấy ra</param>
        /// <param name="tableName">Tên bảng</param>
        /// <returns></returns>
        public DataSet SelectDataSet(string[] fields, string tableName)
        {
            string f = "*";
            if (fields != null && fields.Count() != 0)
            {
                f = string.Empty;
                foreach (string item in fields)
                {
                    f += item + ",";
                }
                f = f.Remove(f.Length - 1);
            }
            string strQuerry = "select " + f + " from " + tableName;
            return ExecuteDataSet(strQuerry, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        /// <param name="orderby"></param>
        /// <returns></returns>
        public DataSet SelectDataSet(string[] fields, string tableName, string orderby)
        {
            try
            {
                if (orderby == null || orderby == string.Empty) return SelectDataSet(fields, tableName);
                string f = "*";
                if (fields != null && fields.Count() != 0)
                {
                    f = string.Empty;
                    foreach (string item in fields)
                    {
                        f += item + ",";
                    }
                    f = f.Remove(f.Length - 1);
                }
                string strQuerry = "select " + f + " from " + tableName + " order by " + orderby;
                return ExecuteDataSet(strQuerry, null);
            }
            catch (Exception)
            {
                return new DataSet();
            }
        }
        public DataSet SelectDataSet(string[] fields, string tableName, SqlParameter[] whereParameters, string orderby)
        {
            try
            {
                if (whereParameters == null || whereParameters.ToList().Count <= 0) return SelectDataSet(fields, tableName, orderby);
                string f = "*";
                if (fields != null && fields.Count() != 0)
                {
                    f = string.Empty;
                    foreach (string item in fields)
                    {
                        f += item + ",";
                    }
                    f = f.Remove(f.Length - 1);
                }
                string strQuerry = "select " + f + " from " + tableName;
                f = string.Empty;
                foreach (SqlParameter item in whereParameters)
                {
                    if (item == whereParameters.ToList()[whereParameters.Length - 1])
                        f += item.ParameterName + " = @" + item.ParameterName;
                    else
                        f += item.ParameterName + " = @" + item.ParameterName + " and ";
                }
                strQuerry += " where " + f;

                strQuerry += " order by " + orderby;
                return ExecuteDataSet(strQuerry, whereParameters);
            }
            catch (Exception)
            {
                return new DataSet();
            }
        }
        #endregion
        #region Select DataTable
        public DataTable SelectDataTable(string tableName)
        {
            string strQuerry = "select * from " + "[" + tableName + "]";
            return ExecuteDataTable(strQuerry, null);
        }
        public DataTable SelectDataTable(string[] fields, string tableName)
        {
            try
            {
                string f = "*";
                if (fields != null && fields.Count() != 0)
                {
                    f = string.Empty;
                    foreach (string item in fields)
                    {
                        f += item + ",";
                    }
                    f = f.Remove(f.Length - 1);
                }
                string strQuerry = "select " + f + " from " + "[" + tableName + "]";
                return ExecuteDataTable(strQuerry, null);
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }
        public DataTable SelectDataTable(string[] fields, string tableName, string orderby)
        {
            try
            {
                if (orderby == null || orderby == string.Empty) return SelectDataTable(fields, tableName);
                string f = "*";
                if (fields != null && fields.Count() != 0)
                {
                    f = string.Empty;
                    foreach (string item in fields)
                    {
                        f += item + ",";
                    }
                    f = f.Remove(f.Length - 1);
                }
                string strQuerry = "select " + f + " from " + "[" + tableName + "]";
                if (orderby != null && orderby != string.Empty)
                    strQuerry += " order by " + orderby;
                return ExecuteDataTable(strQuerry, null);
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }
        public DataTable SelectDataTable(string[] fields, string tableName, SqlParameter[] whereParameters, string orderby)
        {
            try
            {
                if (whereParameters == null || whereParameters.ToList().Count <= 0) return SelectDataTable(fields, tableName, orderby);
                string f = "*";
                if (fields != null && fields.Count() != 0)
                {
                    f = string.Empty;
                    foreach (string item in fields)
                    {
                        f += item + ",";
                    }
                    f = f.Remove(f.Length - 1);
                }
                string strQuerry = "select " + f + " from " + "["+tableName+"]";
                f = string.Empty;
                foreach (SqlParameter item in whereParameters)
                {
                    if (item == whereParameters.ToList()[whereParameters.Length - 1])
                        f += item.ParameterName + " = @" + item.ParameterName;
                    else
                        f += item.ParameterName + " = @" + item.ParameterName + " and ";
                }
                strQuerry += " where " + f;
                if (orderby != null && orderby != string.Empty)
                    strQuerry += " order by " + orderby;
                return ExecuteDataTable(strQuerry, whereParameters);
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }
        #endregion
        #region Select DataReader
        public SqlDataReader SelectReader(string tableName)
        {
            string strQuerry = "select * from " + tableName;
            return ExecuteReader(strQuerry, null);
        }
        public SqlDataReader SelectReader(string[] fields, string tableName)
        {
            if (fields == null || fields.ToList().Count <= 0) return SelectReader(tableName);
            string f = "*";
            if (fields != null && fields.Count() != 0)
            {
                f = string.Empty;
                foreach (string item in fields)
                {
                    f += item + ",";
                }
                f = f.Remove(f.Length - 1);
            }
            string strQuerry = "select " + f + " from " + tableName;
            return ExecuteReader(strQuerry, null);
        }
        public SqlDataReader SelectReader(string[] fields, string tableName, string orderby)
        {
            if (orderby == null || orderby == string.Empty) return SelectReader(fields, tableName);
            string f = "*";
            if (fields != null && fields.Count() != 0)
            {
                f = string.Empty;
                foreach (string item in fields)
                {
                    f += item + ",";
                }
                f = f.Remove(f.Length - 1);
            }
            string strQuerry = "select " + f + " from " + tableName;
            if (orderby != null && orderby != string.Empty)
                strQuerry += " order by " + orderby;
            return ExecuteReader(strQuerry, null);
        }
        public SqlDataReader SelectReader(string[] fields, string tableName, SqlParameter[] whereParameters, string orderby)
        {
            if (whereParameters == null || whereParameters.ToList().Count <= 0) return SelectReader(fields, tableName, orderby);
            string f = "*";
            if (fields != null && fields.Count() != 0)
            {
                f = string.Empty;
                foreach (string item in fields)
                {
                    f += item + ",";
                }
                f = f.Remove(f.Length - 1);
            }
            string strQuerry = "select " + f + " from " + "["+tableName+"]";
            f = string.Empty;
            foreach (SqlParameter item in whereParameters)
            {
                if (item == whereParameters.ToList()[whereParameters.Length - 1])
                    f += item.ParameterName + " = @" + item.ParameterName;
                else
                    f += item.ParameterName + " = @" + item.ParameterName + " and ";
            }
            strQuerry += " where " + f;
            if (orderby != null && orderby != string.Empty)
                strQuerry += " order by " + orderby;
            return ExecuteReader(strQuerry, whereParameters);
        }
        #endregion
        #region Insert
        public object Insert(string tableName, SqlParameter[] valueParameters)
        {
            object obj = new object();
            if (valueParameters == null) return new object();
            string query = "insert into " + "["+tableName+"]";
            string st1 = string.Empty, st2 = string.Empty;
            foreach (SqlParameter item in valueParameters)
            {
                st1 += item.ParameterName + ",";
                st2 += "@" + item.ParameterName + ",";
            }
            st1 = st1.Remove(st1.Length - 1);
            st2 = st2.Remove(st2.Length - 1);
            query += " (" + st1 + ") values(" + st2 + ")";
            obj = ExecuteQuery(query, valueParameters);
            return obj;
        }
        public object Insert(string tableName, SqlParameter[] valueParameters, bool SelectSCOPE_IDENTITY)
        {
            object obj = new object();
            try
            {
                if (valueParameters == null) return new Object();
                Open();
                string query = "insert into " + "[" + tableName + "]";
                string st1 = string.Empty, st2 = string.Empty;
                foreach (SqlParameter item in valueParameters)
                {
                    st1 += item.ParameterName + ",";
                    st2 += "@" + item.ParameterName + ",";
                }
                st1 = st1.Remove(st1.Length - 1);
                st2 = st2.Remove(st2.Length - 1);
                query += " (" + st1 + ") values(" + st2 + ")";
                using (SqlCommand cmd = new SqlCommand(query, connect))
                {
                    if (valueParameters != null)
                        cmd.Parameters.AddRange(valueParameters);
                    obj = (object)cmd.ExecuteNonQuery();
                }
                if (SelectSCOPE_IDENTITY)
                {
                    string strQuerry = "select @@IDENTITY";
                    using (SqlCommand cmd = new SqlCommand(strQuerry, connect))
                    {
                        obj = (object)cmd.ExecuteScalar();
                    }

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Close();
                connect.Dispose();
            }
            return obj;
        }
        #endregion
        #region Update
        public int Update(string tableName, SqlParameter[] valueParameters)
        {
            string strQuerry = "update " + "[" + tableName + "]"+ " set ";
            string s = string.Empty;
            foreach (SqlParameter item in valueParameters)
            {
                s += "[" + item.ParameterName + "]" + "= @" + item.ParameterName + ",";
            }
            s = s.Remove(s.Length - 1);
            strQuerry += " " + s;
            return ExecuteQuery(strQuerry, valueParameters);
        }
        public int Update(string tableName, SqlParameter[] valueParameters, SqlParameter[] whereParameters)
        {
            string strQuerry = "update " + "[" + tableName + "]"+ " set ";
            string s = string.Empty;
            foreach (SqlParameter item in valueParameters)
            {
                s += "[" + item.ParameterName + "]" + "= @" + item.ParameterName + ",";
            }
            s = s.Remove(s.Length - 1);
            strQuerry += " " + s + " where ";
            s = string.Empty;
            foreach (SqlParameter item in whereParameters)
            {
                if (item == whereParameters.ToList()[whereParameters.Length - 1])
                    s += item.ParameterName + "= @" + item.ParameterName;
                else
                    s += item.ParameterName + "= @" + item.ParameterName + " and ";
            }
            strQuerry += s;
            SqlParameter[] Parameters = new SqlParameter[valueParameters.Length + whereParameters.Length];
            int k = 0;
            for (int i = 0; i < valueParameters.Length; i++)
            {
                Parameters[k++] = valueParameters[i];
            }
            for (int i = 0; i < whereParameters.Length; i++)
            {
                Parameters[k++] = whereParameters[i];
            }
            return ExecuteQuery(strQuerry, Parameters);
        }
        #endregion
        #region Delete
        public int Delete(string tableName)
        {
            string strQuerry = "delete from " + "[" + tableName + "]";
            return ExecuteQuery(strQuerry, null);
        }
        public int Delete(string tableName, SqlParameter[] whereParameters)
        {
            string strQuerry = "delete from " + "[" + tableName + "]";
            string s = string.Empty;
            foreach (SqlParameter item in whereParameters)
            {
                if (item == whereParameters.ToList()[whereParameters.Length - 1])
                    s += item.ParameterName + " = @" + item.ParameterName;
                else
                    s += item.ParameterName + " = @" + item.ParameterName + " and ";
            }            
            strQuerry += " where " + s;
            return ExecuteQuery(strQuerry, whereParameters);
        }
        #endregion
        #endregion

    }
}
